package br.com.petshow.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.swing.JOptionPane;

import br.com.petshow.model.GenericModel;
import br.com.petshow.model.Tipos;
import br.com.petshow.utils.SqlField;

public abstract class GenericDao<T> implements IDaoMethods<T> {

	private List<SqlField> fieldsToInsertOrEdit;
	private String tableName;
	private String sequenceName;

	public enum Operacao {
		INSERIR {
			@Override
			public String toString() {
				return "inseri";
			}
		},
		DELETAR {
			@Override
			public String toString() {
				return "delet";
			}
		},
		ATUALIZAR {
			@Override
			public String toString() {
				return "atualiza";
			}
		},
		SELECIONAR {
			@Override
			public String toString() {
				return "seleciona";
			}
		}
	}

	@Override
	public void executeSql(String sql, T obj, Operacao operacao) {
		Connection con = null;
		GenericModel o = (GenericModel) obj;
		try {
			con = FabricaConexao.getConexao();
			PreparedStatement pstm = con.prepareStatement(sql);
			if (operacao != Operacao.DELETAR)
				setFieldsToSql(pstm, o, getFieldsToInsertOrEdit());
			pstm.execute();

			JOptionPane.showMessageDialog(null, "Dado " + operacao
					+ "do com sucesso!");
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null, "Erro ao " + operacao
					+ "r o dado na base de dados! " + e.getMessage());
		} finally {
			try {
				con.close();
			} catch (SQLException e2) {
				e2.printStackTrace();
			}
		}
	}

	@Override
	public T executeQuery(String sql) throws SQLException {
		return executeQuery(sql, null);
	}
	
	@Override
	public T executeQuery(String sql, List<SqlField> filter) throws SQLException {
		Connection con = null;
		T o = null;
		try {
			con = FabricaConexao.getConexao();

			PreparedStatement pstm = con.prepareStatement(sql);
			if (filter != null)
				setFieldsToSql(pstm, (GenericModel)o, filter);
			ResultSet rs = pstm.executeQuery();
			o = tratarRetorno(rs);

		} finally {
			con.close();
		}
		return o;
	}

	@Override
	public List<T> executeQueryList(String sql, List<SqlField> filter) throws SQLException {
		Connection con = null;
		List<T> o = null;
		try {
			con = FabricaConexao.getConexao();

			PreparedStatement pstm = con.prepareStatement(sql);
			if (filter != null)
				setFieldsToSql(pstm, (GenericModel)o, filter);
			ResultSet rs = pstm.executeQuery();
			o = tratarRetornoList(rs);

		} finally {
			con.close();
		}
		return o;
	}

	private String getInsertSql(GenericModel o) {
		StringBuilder sbSql = new StringBuilder();

		sbSql.append("INSERT INTO ");
		sbSql.append(getTableName());
		sbSql.append("(id,");

		for (int i = 0; i < getFieldsToInsertOrEdit().size(); i++) {
			sbSql.append(getFieldsToInsertOrEdit().get(i).getField());
			if (i < (getFieldsToInsertOrEdit().size() - 1)) {
				sbSql.append(",");
			}
		}

		sbSql.append(") VALUES (");
		sbSql.append(o.getId());
		sbSql.append(",");

		for (int i = 0; i < getFieldsToInsertOrEdit().size(); i++) {
			sbSql.append("?");
			if (i < (getFieldsToInsertOrEdit().size() - 1)) {
				sbSql.append(",");
			}
		}

		sbSql.append(")");

		return sbSql.toString();
	}

	private String getEditSql(GenericModel o) {
		StringBuilder sbSql = new StringBuilder();

		sbSql.append("UPDATE ");
		sbSql.append(getTableName());
		sbSql.append(" SET ");

		for (int i = 0; i < getFieldsToInsertOrEdit().size(); i++) {
			sbSql.append(getFieldsToInsertOrEdit().get(i).getField());
			sbSql.append(" = ? ");
			if (i < (getFieldsToInsertOrEdit().size() - 1)) {
				sbSql.append(",");
			}
		}

		sbSql.append(" WHERE id = ");
		sbSql.append(o.getId());

		return sbSql.toString();
	}

	private String getDeleteSql(GenericModel o) {
		StringBuilder sbSql = new StringBuilder();

		sbSql.append("DELETE FROM ");
		sbSql.append(getTableName());

		sbSql.append(" WHERE id = ");
		sbSql.append(o.getId());

		return sbSql.toString();
	}

	private String getSelectSql(List<SqlField> filter) {
		StringBuilder sb = new StringBuilder("SELECT * FROM ");
		sb.append(getTableName());

		if (filter != null) {
			sb.append(" WHERE 1 = 1 ");
			
			for (int i = 0; i < filter.size(); i++) {
				SqlField field = filter.get(i);
				sb.append("AND ");
				sb.append(field.getField());
				if (field.getValue() instanceof String) {
					sb.append(" LIKE ?");
				} else {
					sb.append(" = ?");
				}
			}
		}

		return sb.toString();
	}

	private void setFieldsToSql(PreparedStatement pstm, GenericModel o, List<SqlField> fields)
			throws SQLException {
		for (int i = 0; i < fields.size(); i++) {
			SqlField field = fields.get(i);
			String value = String.valueOf(field.getValue());
			if (field.getValue() instanceof String) {
				pstm.setString(i + 1, value);
			}
			if (field.getValue() instanceof Integer) {
				pstm.setInt(i + 1, Integer.valueOf(value));
			}
			if (field.getValue() instanceof Date) {
				pstm.setDate(i + 1, Date.valueOf(value));
			}
			if (field.getValue() instanceof Double) {
				pstm.setDouble(i + 1, Double.valueOf(value));
			}
		}
	}

	@Override
	public void insertOrUpdate(T obj) throws SQLException {
		GenericModel o = (GenericModel) obj;
		if (o.getId() == null) {
			o.setId(getNextId());
			executeSql(getInsertSql(o), obj, Operacao.INSERIR);
		} else {
			executeSql(getEditSql(o), obj, Operacao.ATUALIZAR);
		}

	}

	@Override
	public void delete(T o) {
		executeSql(getDeleteSql((GenericModel) o), o, Operacao.DELETAR);
	}

	@Override
	public List<T> getList() throws SQLException {
		return getList(null);
	}

	public java.util.List<T> getList(List<SqlField> filters) throws SQLException {
		return executeQueryList(getSelectSql(filters), filters);
	};

	@Override
	public Integer getNextId() throws SQLException {
		Integer nextId = null;

		StringBuilder sbSql = new StringBuilder("SELECT ");
		sbSql.append(getSequenceName());
		sbSql.append(".nextval AS id FROM DUAL");

		T o = executeQuery(sbSql.toString());
		nextId = ((GenericModel) o).getId();

		return nextId;
	}

	@Override
	public T getObjectById(Integer id) throws SQLException {
		List<SqlField> filter = new ArrayList<SqlField>();
		SqlField field = new SqlField("id",id);
		filter.add(field);
		return executeQuery(getSelectSql(filter), filter);
	}

	public List<SqlField> getFieldsToInsertOrEdit() {
		return fieldsToInsertOrEdit;
	}

	public void setFieldsToInsertOrEdit(List<SqlField> fieldsToInsertOrEdit) {
		this.fieldsToInsertOrEdit = fieldsToInsertOrEdit;
	}

	public String getSequenceName() {
		return sequenceName;
	}

	public void setSequenceName(String sequence) {
		this.sequenceName = sequence;
	}

	public String getTableName() {
		return tableName;
	}

	public void setTableName(String tableName) {
		this.tableName = tableName;
	}

}
